import connector.DatabaseDriver
from connector.DatabaseDriver import DatabaseDriver


def search_goods(driver: DatabaseDriver, text: str, goods_id: int = None):
    # goods_id
    if goods_id is None:
        # 说明现在是文本查询
        results: tuple = driver.select_all('select * from goods where goods_name like "%{}%" or company like "%{}%"'.format(text, text))
        return results
    else:
        # 现在是根据商品号查询
        results: tuple = driver.select_all('select * from goods where goods_id={}'.format(goods_id))
        return results


def get_all_goodss(driver: DatabaseDriver) -> tuple:
    results: tuple = driver.select_all('select * from goods')
    return results


# 根据用户id查询是否有待还的书
def get_borrow_goods_by_user_id(driver: DatabaseDriver, goods_id: int):
    # select * from borrow where user_id ={} and return_time is Null
    if goods_id is not None:
        # return driver.select_all("select * from borrow where user_id ={} and return_time is Null".format(user_id))
        # select borrow.*,goods.goods_name,goods.company,goods,save_loc from goods,borrow where
        # goods.goods_id=borrow.goods_id and borrow.user_id=1 and return_time is NULL
        return driver.select_all("select in/outstorage.*, goods.goods_name ,goods.company, goods.save_loc "
                                 "from goods,in/outstorage where goods.goods_id = in/outstorage.goods_id and in/outstorage.user_id = {} and "
                                 "return_time is NULL".format(goods_id))


def get_goods_by_name_company_type(driver: DatabaseDriver, goods_name: str = '', company: str = '', goods_type: int = None) -> tuple:
    if goods_name == '' and company == '' and goods_type == -1:
        return get_all_goodss(driver)
    else:
        if goods_type == -1:
            goods_type = None
        sql: str = ''
        #
        if goods_name != '' and company != '' and goods_type is not None:
            sql: str = 'select * from goods where goods_name like "%{}%" and company like "%{}%" and cate_id={}'.format(goods_name, company, goods_type)
        elif goods_name != '' and company != '':
            sql: str = 'select * from goods where goods_name like "%{}%" and company like "%{}%"'.format(goods_name, company)
        elif goods_name != '' and goods_type is not None:
            sql: str = 'select * from goods where goods_name like "%{}%" and cate_id={}'.format(goods_name, goods_type)
        elif company != '' and goods_type is not None:
            sql: str = 'select * from goods where  company like "%{}%" and cate_id={}'.format(company, goods_type)
        elif goods_name != '':
            sql: str = 'select * from goods where goods_name like "%{}%"'.format(goods_name)
        elif company != '':
            sql: str = 'select * from goods where company like "%{}%"'.format(company)
        elif goods_type != '':
            sql: str = 'select * from goods where cate_id={}'.format(goods_type)
        return driver.select_all(sql=sql)


def user_search_goods_by_goods_id(driver: DatabaseDriver, goods_id: int):
    results: tuple = driver.select_all('select * from goods where goods_id={}'.format(goods_id))
    return results


# 修改书本
def update_goods(driver: DatabaseDriver, goods_name: str, goods_company: str,  cate_id: int, nums: int,
                save_loc: str) -> int:
    return driver.update(
        'update goods set goods_name="{}",company="{}",cate_id={},nums={},sava_loc="{}" where goods_id={}'.format(
            goods_name, goods_company, cate_id, nums, save_loc))


# 插入新书本
def insert_goods(driver: DatabaseDriver, goods_name: str, goods_publisher: str, goods_company: str, cate_id: int, nums: int,
                save_loc: str) -> int:
    return driver.insert(
        'insert into goods(goods_name,company , cate_id, nums, save_loc) values ("{}","{}",{},{},"{}")'.format(
            goods_name, goods_publisher, goods_company, cate_id, nums, save_loc))


# 删除书本
def delete_goods(driver: DatabaseDriver, goods_id: int) -> int:
    return driver.delete('delete from goods where goods_id={}'.format(goods_id))